package com.fpt.group3.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import com.fpt.group3.form.TrustRegion;
import com.fpt.group3.service.exception.SystemException;

/**
 * process to data into TrustRegion.
 * @author John Tran
 */
public class TrustRegionDAOImpl implements TrustRegionDAO {

    @Override
    public final boolean addTrustRegion(final TrustRegion tr)
            throws SystemException {
        String sql = "INSERT INTO trustregion(NAME_TR,"
                + " DESCRIPTION_TR, COUNTRYID, STATUS_TR) VALUES (?,?,?,?)";
        PreparedStatement pstmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            pstmt = conn.open().prepareStatement(sql);
            int i = 1;
            pstmt.setString(i++, tr.getName());
            pstmt.setString(i++, tr.getDescription());
            pstmt.setInt(i++, tr.getCountryID());
            pstmt.setInt(i++, 1);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } catch (SystemException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public final boolean amendTrustRegion(final TrustRegion tr)
            throws SystemException {
        String sql = "UPDATE trustregion SET NAME_TR = ?,"
                + " DESCRIPTION_TR = ?, COUNTRYID = ? WHERE TRUSTREGIONID = ?";
        PreparedStatement pstmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            pstmt = conn.open().prepareStatement(sql);
            int i = 1;
            pstmt.setString(i++, tr.getName());
            pstmt.setString(i++, tr.getDescription());
            pstmt.setInt(i++, tr.getCountryID());
            pstmt.setInt(i++, tr.getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } catch (SystemException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

<<<<<<< .mine
    @Override
    public final boolean changTrustRegionStatus(final int trID,
            final int active) throws SystemException {
        String sql = "UPDATE trustregion"
                + " SET STATUS_TR = ? WHERE TRUSTREGIONID = ?";
        PreparedStatement pstmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            pstmt = conn.open().prepareStatement(sql);
            pstmt.setInt(1, active);
            pstmt.setInt(2, trID);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } catch (SystemException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public final LinkedList<TrustRegion> listTrustRegion(final boolean active)
            throws SystemException {
        LinkedList<TrustRegion> trustRegionList = new LinkedList<TrustRegion>();
        TrustRegion tr = null;
        String sql = "";
        if (active) {
            sql = "SELECT NAME_TR, DESCRIPTION_TR,"
                    + " COUNTRYNAME, STATUS_TR FROM TrustRegion,"
                    + " country WHERE TrustRegion.COUNTRYID"
                    + " = country.COUNTRYID and STATUS_TR = 1";
        } else {
            sql = "SELECT NAME_TR, DESCRIPTION_TR,"
                    + " COUNTRYNAME, STATUS_TR FROM TrustRegion,"
                    + " country WHERE TrustRegion.COUNTRYID"
                    + " = country.COUNTRYID";
        }
        Statement stmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            Connection con = conn.open();
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                tr = new TrustRegion();
                // tr.setId(rs.getInt("TRUSTREGIONID"));
                tr.setName(rs.getString("NAME_TR"));
                tr.setDescription(rs.getString("DESCRIPTION_TR"));
                tr.setCountryName(rs.getString("COUNTRYNAME"));
                String isActive = "";
                if (rs.getInt("STATUS_TR") == 1) {
                    isActive = "Yes";
                } else {
                    isActive = "No";
                }
                tr.setIsActive(isActive);
                trustRegionList.add(tr);
            }
        } catch (SystemException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return trustRegionList;
    }

    /**
     * Get Trust Region Id by Name.
     * @param reName region name
     * @return region id
     * @throws SystemException throws exception
     */
    public final int getRegionIdByName(final String reName)
            throws SystemException {

        String sql = "SELECT TRUSTREGIONID FROM"
                + " trustregion WHERE NAME_TR = ?";
        PreparedStatement pstmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            pstmt = conn.open().prepareStatement(sql);
            System.out.println("Region Name = " + reName);
            pstmt.setString(1, reName);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } catch (SystemException e) {
            e.printStackTrace();
            return -1;
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return -1;
    }
=======
    @Override
    public final boolean changTrustRegionStatus(final int trID,
            final int active) throws SystemException {
        String sql = "UPDATE trustregion"
                + " SET STATUS_TR = ? WHERE TRUSTREGIONID = ?";
        PreparedStatement pstmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            pstmt = conn.open().prepareStatement(sql);
            pstmt.setInt(1, active);
            pstmt.setInt(2, trID);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } catch (SystemException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public final LinkedList<TrustRegion> listTrustRegion(final boolean active)
            throws SystemException {
        LinkedList<TrustRegion> trustRegionList = new LinkedList<TrustRegion>();
        TrustRegion tr = null;
        String sql = "";
        if (active) {
            sql = "SELECT NAME_TR, DESCRIPTION_TR,"
                    + " COUNTRYNAME, STATUS_TR FROM TrustRegion,"
                    + " country WHERE TrustRegion.COUNTRYID"
                    + " = country.COUNTRYID and STATUS_TR = 1";
        } else {
            sql = "SELECT NAME_TR, DESCRIPTION_TR,"
                    + " COUNTRYNAME, STATUS_TR FROM TrustRegion,"
                    + " country WHERE TrustRegion.COUNTRYID"
                    + " = country.COUNTRYID";
        }
        Statement stmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            Connection con = conn.open();
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                tr = new TrustRegion();
                // tr.setId(rs.getInt("TRUSTREGIONID"));
                tr.setName(rs.getString("NAME_TR"));
                tr.setDescription(rs.getString("DESCRIPTION_TR"));
                tr.setCountryName(rs.getString("COUNTRYNAME"));
                String isActive = "";
                if (rs.getInt("STATUS_TR") == 1) {
                    isActive = "Yes";
                } else {
                    isActive = "No";
                }
                tr.setIsActive(isActive);
                trustRegionList.add(tr);
            }
        } catch (SystemException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return trustRegionList;
    }

    /**
     * Get Trust Region Id by Name.
     * @param reName region name
     * @return region id
     * @throws SystemException throws exception
     */
    public final int getRegionIdByName(final String reName)
            throws SystemException {

        String sql = "SELECT TRUSTREGIONID FROM"
                + " trustregion WHERE NAME_TR = ?";
        PreparedStatement pstmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            pstmt = conn.open().prepareStatement(sql);
            pstmt.setString(1, reName);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } catch (SystemException e) {
            e.printStackTrace();
            return -1;
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return -1;
    }
>>>>>>> .r18
}
